--Create a table
CREATE TABLE Employee
(
EmpID int PRIMARY KEY,
FirstName varchar(50) NULL,
LastName varchar(50) NULL,
Salary int NULL,
Address varchar(100) NULL,
)
--Insert Data
Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(1,'Mohan','Chauahn',22000,'Delhi');
Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(2,'Asif','Khan',15000,'Delhi');
Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(3,'Bhuvnesh','Shakya',19000,'Noida');
Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(4,'Deepak','Kumar',19000,'Noida');
--See created table
Select * from Employee
--Create function to get emp full name Create function fnGetEmpFullName
(
@FirstName varchar(50),
@LastName varchar(50)
)
returns varchar(101)
As
Begin return (Select @FirstName + ' '+ @LastName);
end
--Calling the above created function
Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee
--Create function to get employees
Create function fnGetEmployee()
returns Table
As
return (Select * from Employee)
--Now call the above created function
Select * from fnGetEmployee()
--Create function for EmpID,FirstName and Salary of Employee
Create function fnGetMulEmployee()
returns @Emp Table
(
EmpID int,
FirstName varchar(50),
Salary int
)
As
begin
Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;
--Now update salary of first employee
update @Emp set Salary=25000 where EmpID=1;
--It will update only in @Emp table not in Original Employee table
return
end
--Now call the above created function
Select * from fnGetMulEmployee()
--Now see the original table. This is not affected by above function update command
Select * from Employee
A (SQL) function is a database object in SQL Server. Primarily, this is a set of SQL statements that accept only input parameters, perform actions and return the result. The function can return an only a single value or a table. We can’t use a function to Insert, Update, Delete records in the database tables.
Unlike Stored Procedure, Function returns an only single value.
Unlike Stored Procedure, Function accepts only input parameters.
Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in a database table(s).
Like Stored Procedure, Function can be nested up to 32 levels.
User Defined Function can have up to 1023 input parameters while a Stored Procedure can have up to 2100 input parameters.
User Defined Function can't return XML Data Type.
User Defined Function doesn't support Exception handling.
User Defined Function can call only Extended Stored Procedure.
User Defined Function doesn't support set options like set ROWCOUNT etc.
Liked By
Write Answer
What is the role of Functions in SQL server ?
Join MindStick Community
You have need login or register for voting of answers or question.
Anonymous User
14-Jun-2019EXAMPLE:-
For practice only.
Anonymous User
14-Jun-2019A (SQL) function is a database object in SQL Server. Primarily, this is a set of SQL statements that accept only input parameters, perform actions and return the result. The function can return an only a single value or a table. We can’t use a function to Insert, Update, Delete records in the database tables.